﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;


namespace CCPSIP
{
    public class OracleKit
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }


        /// <summary>
        /// 檢查員工的身分證後四碼是否正確
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <param name="IDNCheck4">身分證後四碼</param>
        /// <returns>正確為true，否則false</returns>
        public static bool ValidateIDN(string EmpID, string IDNCheck4)
        {
            if (EmpID == null || IDNCheck4 == null)
                return false;
            else if (EmpID.Length < 5 || IDNCheck4.Length != 4)
                return false;

            IDNCheck4 = IDNCheck4.ToUpper();

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStrOra = "SELECT ID_NUMBER FROM person_all"
                  + " WHERE IS_ACTIVE IN ('A','T') AND PPS_CODE=:PPS_CODE";
                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID.ToUpper();
                string IDNumber = Convert.ToString(selCmdOra.ExecuteScalar());
                if (string.IsNullOrEmpty(IDNumber) || IDNumber.Length < 8)
                    return false;
                else if (IDNumber.ToUpper().EndsWith(IDNCheck4))
                    return true;
                else
                    return false;
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 取得 UDID 裝置對應的 CCPSIP 帳號(舊版1.6版以前)
        /// </summary>
        /// <param name="udid">iDevice UDID</param>
        /// <returns>CCPSIP 帳號</returns>
        public static string getMsgUserID(string udid)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT MSG_UID FROM ccpsip_user WHERE Status='1' AND UDID=:UDID";
                OracleCommand countCmd = new OracleCommand(selStr, connOracle);
                countCmd.Parameters.Add("UDID", OracleType.VarChar).Value = udid;
                return Convert.ToString(countCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 儲存來電待推播訊息
        /// </summary>
        /// <param name="receiver">受話號碼</param>
        /// <param name="caller">發話號碼</param>
        /// <param name="isVideo">視訊來電為true，否則false</param>
        public static void InsertCallPush(string receiver, string caller, bool isVideo)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                DateTime reqTime = DateTime.Now;

                connOracle.Open();

                string insStr = "INSERT INTO call_push"
                  + " (CreateTime, Status, Receiver, Caller, IsVideo)"
                  + " VALUES (:CreateTime, :Status, :Receiver, :Caller, :IsVideo)";
                List<OracleParameter> paramList = new List<OracleParameter>();
                paramList.Add(new OracleParameter("CreateTime", reqTime));
                paramList.Add(new OracleParameter("Status", "0"));
                paramList.Add(new OracleParameter("Receiver", receiver));
                paramList.Add(new OracleParameter("Caller", caller));
                paramList.Add(new OracleParameter("IsVideo", (isVideo ? "Y" : "N")));
                OracleCommand countCmd = new OracleCommand(insStr, connOracle);
                countCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                countCmd.Parameters.Add("Status", OracleType.VarChar).Value = "0";
                countCmd.Parameters.Add("Receiver", OracleType.VarChar).Value = receiver;
                countCmd.Parameters.Add("Caller", OracleType.VarChar).Value = caller;
                countCmd.Parameters.Add("IsVideo", OracleType.VarChar).Value = (isVideo ? "Y" : "N");
                countCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>
        /// 檢查員工的出生日期是否正確
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <param name="BirthDate">出生日期，格式為yyyyMMdd</param>
        /// <returns>正確為true，否則false</returns>
        public static bool ValidateBirth(string EmpID, string BirthDate)
        {
            if (EmpID == null || BirthDate == null)
                return false;
            else if (EmpID.Length < 5 || BirthDate.Length != 8)
                return false;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStrOra = "SELECT TO_CHAR(BIRTH_DATE,'YYYYMMDD') FROM person_all"
                  + " WHERE IS_ACTIVE IN ('A','T') AND PPS_CODE=:PPS_CODE";
                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID.ToUpper();
                string BIRTH_DATE = Convert.ToString(selCmdOra.ExecuteScalar());
                if (string.IsNullOrEmpty(BIRTH_DATE))
                    return false;
                else if (BIRTH_DATE == BirthDate)
                    return true;
                else
                    return false;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得員工姓名
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>員工姓名</returns>
        public static string getFullName(string EmpID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStrOra = "SELECT NAME FROM isender_person_all"
                //string selStrOra = "SELECT NAME FROM PERSON_ALL_MVIEW"
                  + " WHERE PPS_CODE=:PPS_CODE"
                  + " ORDER BY EMPLOYMENT_DATE DESC";
                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID.ToUpper();
                return Convert.ToString(selCmdOra.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }

        /// <summary>取得員工DUTY_CODE</summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>DUTY_CODE</returns>
        /// 2014/12/10 有個資法疑慮，先不使用
        public static string GetDutyCode(string pps_code)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            string cmd_all = "SELECT DUTY_CODE"
                           + " FROM PERSON_ALL_MVIEW"
                           + " WHERE PPS_CODE=:PPS_CODE"
                           + " AND IS_ACTIVE IN ('A', 'T')";
            connOracle.Open();

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            cmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = pps_code.ToUpper();

            string ReturnDutyCode = Convert.ToString(cmd.ExecuteScalar());
            connOracle.Dispose();

            return ReturnDutyCode;
        }

        /// <summary>取得部長級以上DUTY_CODE</summary>
        /// <returns>DUTY_CODE</returns>
        /// 2014/12/10 有個資法疑慮，先不使用
        public static List<string> GetDutyCodesAboveDepartmentLeader()
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            string cmd_all = "SELECT DISTINCT DUTY_CODE"
                            + " FROM PERSON_ALL_MVIEW"
                            + " WHERE DUTY_CODE <= 'BK'"        //部長級以上
                            + " AND COMPANY NOT IN ('8', 'V')"  //不含台豐
                            + " AND IS_ACTIVE IN ('A', 'T')"
                            + " ORDER BY DUTY_CODE";
            connOracle.Open();

            OracleCommand cmd = new OracleCommand(cmd_all, connOracle);
            OracleDataReader reader = cmd.ExecuteReader();

            List<string> ReturnDutyCodesAboveDepartmentLeader = new List<string>();
            while (reader.Read())
            {
                ReturnDutyCodesAboveDepartmentLeader.Add(Convert.ToString(reader["DUTY_CODE"]));
            }
            reader.Dispose();
            connOracle.Dispose();

            return ReturnDutyCodesAboveDepartmentLeader;
        }
    }
}